USE [EMPRESA_TEST]
GO
/****** Objeto:  StoredProcedure [dbo].[ver_cliente_deudor]    Fecha de la secuencia de comandos: 11/10/2017 10:22:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[ver_cliente_deudor] 
       @tipo_documento varchar(3),
       @nro_documento  varchar(15),
       @tiene_deuda smallint=0 OUTPUT AS

DECLARE
@Saldo decimal(18, 5),
@Pedidos decimal(18, 5),
@Credito decimal(18, 5),
@Codctacte char(8)

  SELECT @Codctacte=isnull(RTRIM(Codctacte),'')  
     FROM Tabla1 T1 (nolock)
    WHERE Cueprefi='C' 
      AND Nrodoc1 = @nro_documento

  SELECT @Credito=isnull(Credglobal,0)  
     FROM Tabla1 T1 (nolock)
    WHERE Cueprefi='C' 
      AND Nrodoc1 = @nro_documento

  SELECT @Saldo=(SELECT isnull(sum(T2.Saldo*T2.Signo), 0)
	FROM Tabla2 T2 (nolock)
		JOIN Tabla3 T3 (nolock) ON (T3.Nrotrans=T2.Nrotrans)
	WHERE T3.Nrotranselim is null AND 
              T3.Cueprefi='C' AND 
              T3.Codctacte=@codctacte AND 
	      T3.nrotransaut is not null AND 
	      T2.Fechavto < getdate() AND
              T2.Nrotransegr is null)

  SELECT @Pedidos=(SELECT isnull(sum((T4.cantidad - T4.cantusada)*T4.precio),0)
   FROM Tabla3 T3 (nolock)
	JOIN Tabla4 T4 (nolock) ON (T3.Nrotrans=T4.Nrotrans)
   WHERE T3.Nrotranselim is null AND 
	 T3.Cueprefi='C' AND 
     T3.Codctacte=@codctacte AND 
	 T3.codcmp='PV' AND 
--         T4.cantidad<>0) 
     T4.usado = -1 AND 
     datepart (yyyy, T4.FECHAENT) <= datepart (yyyy, getdate()) AND
     datepart (mm, T4.FECHAENT) < datepart (mm, getdate()))


  if @Saldo + @Pedidos > @Credito
     select @tiene_deuda = 1
  else
    begin
      if @Codctacte IS NULL 
        select @tiene_deuda = 9
      else
        select @tiene_deuda = 0
    end


--return = 1 cliente tiene deuda
--return = 9 cliente no existe en bas
--return = 0 esta OK!
